/**
 * 
 */
package Modelo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

/**
 * @author julio
 *
 */
public class Oracle implements InterfazBD {

	private Connection conexion;
	
	public Oracle(Connection conexion) {
		this.conexion=conexion;
	}

	public Vector obtener (String[] campo, String[] tabla){
		Vector listado;
		listado = new Vector();
		ResultSet rs = null;
		Statement st = null;
		try{
			
			String ordenSQL = "SELECT ";
	        for (int i =0; i<campo.length;i++){
	        	if (i!=0)
	        		ordenSQL+= ", ";
	        	ordenSQL+=campo[i];
	        }
	        ordenSQL+=" FROM ";
	        for(int i=0; i<tabla.length; ++i){
	        	if (i!=0)
	        		ordenSQL+=", ";
	        	ordenSQL += tabla[i];
	        }
			
            st = conexion.createStatement();
            rs = st.executeQuery(ordenSQL);
            while(rs.next()){
            	String[] datos= new String [campo.length];
            	for(int i=0;i<campo.length;i++){
            		datos[i]=(rs.getString(campo[i]));
            	}
            	listado.add(datos);
            }
            if(rs != null)
				rs.close();
			if(st != null)
				st.close();
		}catch(SQLException se){
			System.err.println(se);
		}catch(Exception e){
			System.err.println(e);
		}finally {
			
		}
		return listado;
	}
	
	public Vector obtener (String[] campo, String[] tabla, String[] condicion) {
		Vector listado;
		listado = new Vector();
		ResultSet rs = null;
		Statement st = null;
		String ordenSQL = "SELECT ";
        
		for(int i=0; i<campo.length;i++){
        	if (i!=0)
        		ordenSQL+= ", ";
        	ordenSQL+=campo[i];
        }
		ordenSQL+=" FROM ";
        for(int i=0; i<tabla.length; ++i){
        	if (i!=0)
        		ordenSQL+=", ";
        	ordenSQL += tabla[i];
        }
        
        ordenSQL+=" WHERE ";
        for (int i=0; i<condicion.length; ++i){
        	if (i!=0)
        		ordenSQL+=" AND ";
        	ordenSQL+=condicion[i];
        }
		try{
            st = conexion.createStatement();
            rs = st.executeQuery(ordenSQL);
            while(rs.next()){
            	String[] datos= new String [campo.length];
            	for(int i=0;i<campo.length;i++){
            		datos[i]=(rs.getString(campo[i]));
            	}
            	listado.add(datos);
            }
            if(rs != null)
				rs.close();
			if(st != null)
				st.close();
		}catch(SQLException se){
			System.err.println(se);
		}catch(Exception e){
			System.err.println(e);
		}finally {
			
		}
		return listado;
	}
	
	public Vector obtenerDistinct (String[] campo, String[] tabla){
		Vector listado;
		listado = new Vector();
		ResultSet rs = null;
		Statement st = null;
		try{
			
			String ordenSQL = "SELECT DISTINCT ";
	        for (int i =0; i<campo.length;i++){
	        	if (i!=0)
	        		ordenSQL+= ", ";
	        	ordenSQL+=campo[i];
	        }
	        ordenSQL+=" FROM ";
	        for(int i=0; i<tabla.length; ++i){
	        	if (i!=0)
	        		ordenSQL+=", ";
	        	ordenSQL += tabla[i];
	        }
			
            st = conexion.createStatement();
            rs = st.executeQuery(ordenSQL);
            while(rs.next()){
            	String[] datos= new String [campo.length];
            	for(int i=0;i<campo.length;i++){
            		datos[i]=(rs.getString(campo[i]));
            	}
            	listado.add(datos);
            }
            if(rs != null)
				rs.close();
			if(st != null)
				st.close();
		}catch(SQLException se){
			System.err.println(se);
		}catch(Exception e){
			System.err.println(e);
		}finally {
			
		}
		return listado;
	}
	
	public Vector obtenerArticulo(){        
		Vector listado;
		listado = new Vector();
		ResultSet rs = null;
		Statement st = null;
        try{
        	String ordenSQL = "SELECT DISTINCT RTRIM(SUBSTR(descripcion, 0, INSTR(descripcion, ' ')+6)) descrip FROM articulo ORDER BY DESCRIP";
        	st = conexion.createStatement();
            rs = st.executeQuery(ordenSQL);
            //instruccion en sql para cojer los campos de la tabla
            
            while(rs.next()){  // se crean los clientes uno a uno y se introducen una a una en el vector
                listado.add(rs.getString("descrip"));
            }
            if(rs != null)
				rs.close();
			if(st != null)
				st.close();
		}catch(SQLException se){
			System.err.println(se);
		}catch(Exception e){
			System.err.println(e);
		}finally {
			
		}
		return listado;
    }
	
	
	public Vector obtenerDistinct (String[] campo, String[] tabla, String[] condicion){
		Vector listado;
		listado = new Vector();
		ResultSet rs = null;
		Statement st = null;
		
		String ordenSQL = "SELECT DISTINCT ";
        
		for(int i=0; i<campo.length;i++){
        	if (i!=0)
        		ordenSQL+= ", ";
        	ordenSQL+=campo[i];
        }
		ordenSQL+=" FROM ";
        for(int i=0; i<tabla.length; ++i){
        	if (i!=0)
        		ordenSQL+=", ";
        	ordenSQL += tabla[i];
        }
        ordenSQL+=" WHERE ";
        for (int i=0; i<condicion.length; ++i){
        	if (i!=0)
        		ordenSQL+=" AND ";
        	ordenSQL+=condicion[i];
        }
		try{
            st = conexion.createStatement();
            rs = st.executeQuery(ordenSQL);
            while(rs.next()){
            	String[] datos= new String [campo.length];
            	for(int i=0;i<campo.length;i++){
            		datos[i]=(rs.getString(campo[i]));
            	}
            	listado.add(datos);
            }
            if(rs != null)
                rs.close();
            if(st != null)
                st.close();
		}catch(SQLException se){
			System.err.println(se);
		}catch(Exception e){
			System.err.println(e);
		}finally {
			
		}
		return listado;
	}
	
	
	 
	
}
